Accessing Excel Named ranges

Hi all,

I'm working on a metrics collection routine and want to write data to Excel

Using the various resources from the forum, I can write to Excel cells just fine.

What I want to do, however, is to not have fixed cell addresses in my script where to write to, but I want to write to the cells defined by name (i.e. so-called Excel named ranges).

Using OLE automation, I can get the "ActiveWorkbook.Names.Count" attribute to get the number of defined named ranges. What I cannot figure out, is how to access the name of the first element in the collection (i.e. ActiveWorkbook.Names(1).Name).

I have no problem doing exactly the same with ListObjects in Excel (i.e. the Excel tables); i.e. get the count, then iterate over all the names of the ListObjects collection and then get the needed ListObject instance.

Some code, to explain (assume oActiveWorkbook is a valid OleAutoObj):

string sErr = null, sName = null
int iCount

OleAutoObj oTarget, oNamesCollection, oTablesCollection, oActiveSheet

OleAutoArgs oleArgs = create()

clear oleArgs
put(oleArgs, 1)

sErr = oleGet(oActiveWorkbook, "Sheets", oleArgs, oFirstSheet)
print "0 " sErr "\n"

sErr = oleGet(oFirstSheet, "ListObjects", oTablesCollection)
print "1 " sErr "\n"
sErr = oleGet(oTablesCollection, "Count", iCount)
print "2 " sErr "\n"
print ">> " iCount " tables found\n" // PRINTS ">> 1 tables found"
clear oleArgs
put(oleArgs, 1)
sErr = oleGet(oFirstSheet, "ListObjects", oleArgs, oTarget)
print "3 " sErr "\n" // NO ERROR HERE

print "\n"

sErr = oleGet(oActiveWorkbook, "Names", oNamesCollection)
print "1 " sErr "\n"
sErr = oleGet(oNamesCollection, "Count", iCount)
print "2 " sErr "\n"
print ">> "iCount " named ranges found\n" // PRINTS ">> 3 named ranges found"
clear oleArgs
put(oleArgs, 1)
sErr = oleGet(oActiveWorkbook, "Names", oleArgs, oTarget)
print "3 " sErr "\n" // THIS PRODUCES AN ERROR

delete oleArgs

Anyone know why this is, or more importantly, how to fix this? I'd really like to use this Excel feature.

Thanks!

Re: Accessing Excel Named ranges
Doug.Zawacki - Tue Jan 21 09:34:28 EST 2014

This should work for you. NOTE: I don't post often and can't seem to figure out how to indicate code blocks, sorry.

<code>

sErr = oleGet(objWorkbook, "Names", oNamesCollection)
print "1 " sErr "\n"
sErr = oleGet(oNamesCollection, "Count", iCount)
print "2 " sErr "\n"
print ">> "iCount " named ranges found\n" // PRINTS ">> 3 named ranges found"

int i
string strName = ""
string strRefersTo = ""
for(i=1;i<iCount;i++)
{
  clear oleArgs
  put(oleArgs, i)
 
  oleMethod(oNamesCollection, "Item", oleArgs, oTarget)
  oleGet(oTarget, "Name", strName)
  oleGet(oTarget, "RefersTo", strRefersTo)

  print "Name: " strName "\t" strRefersTo "\n"
}

</code>

Re: Accessing Excel Named ranges
llandale - Tue Jan 21 12:28:00 EST 2014

I didn't study the code, but this looks wrong:

• for(i=1;i<iCount;i++)

I may be wrong of course, but I think it is more likely one of these:

1. for(i=0;i<iCount;i++)     // start from 0
2. for(i=1;i<=iCount;i++)  // or equal

-Louie

Re: Accessing Excel Named ranges
Doug.Zawacki - Tue Jan 21 12:33:25 EST 2014

Yes Louie, You are correct. Sorry, I didn't check my looping parameter. #2 is the correct one... (for(i=1;i<=iCount;i++)

Thanks for that catch !

Re: Accessing Excel Named ranges
llandale - Wed Jan 22 12:10:42 EST 2014

Well, when you make 100s of loop parameter mistakes you get used to looking for them.

Re: Accessing Excel Named ranges
M_vdLaan - Thu Jan 23 04:22:18 EST 2014

Hi Doug,

Thanks! Seems to do the trick nicely (with the loop condition modified as suggested by Louis).

I managed to find the range by name, but this allows me to get all the names in the workbook, both those at workbook scope and at worksheet scope. Nice! I had already tried the alternative with the additional parameters, but not with them being empty. 

Marcel